ALTER DATABASE (Transact

Modifies certain configuration options of a database.

This article provides the syntax, arguments, remarks, permissions, and examples for whichever SQL product you choose.

For more information about the syntax conventions, see Transact-SQL syntax conventions.

In the following row, select the product name you're interested in, and only that product's information is displayed.

* SQL Server *  

SQL Database

SQL Managed Instance

Azure SynapseAnalytics

Analytics PlatformSystem (PDW)

In SQL Server, this statement modifies a database, or the files and filegroups associated with the database. ALTER DATABASE adds or removes files and filegroups from a database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options. Database snapshots can't be modified. To modify database options associated with replication, use sp_replicationdboption.

Because of its length, the ALTER DATABASE syntax is separated into the multiple articles.

Is the name of the database to be modified.

Note

This option isn't available in a Contained Database.

CURRENT Applies to: SQL Server 2012 (11.x) and later.

Designates that the current database in use should be altered.

Renames the database with the name specified as new_database_name.

Specifies the collation for the database. collation_name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the collation of the instance of SQL Server.

Note

Collation can't be changed after database has been created on Azure SQL Database.

When creating databases with other than the default collation, the data in the database always respects the specified collation. For SQL Server, when creating a contained database, the internal catalog information is maintained using the SQL Server default collation, Latin1_General_100_CI_AS_WS_KS_SC.

For more information about the Windows and SQL collation names, see COLLATE.

Applies to: SQL Server 2014 (12.x) and later.

For more information, see ALTER DATABASE SET options and Control Transaction Durability.

For more information, see ALTER DATABASE File and Filegroup Options.

To remove a database, use DROP DATABASE.

To decrease the size of a database, use DBCC SHRINKDATABASE.

The ALTER DATABASE statement must run in auto-commit mode (the default transaction management mode) and isn't allowed in an explicit or implicit transaction.

The state of a database file (for example, online or offline), is maintained independently from the state of the database. For more information, see File States. The state of the files within a filegroup determines the availability of the whole filegroup. For a filegroup to be available, all files within the filegroup must be online. If a filegroup is offline, any attempt to access the filegroup by an SQL statement fails with an error. When you build query plans for SELECT statements, the query optimizer avoids nonclustered indexes and indexed views that reside in offline filegroups. This enables these statements to succeed. However, if the offline filegroup contains the heap or clustered index of the target table, the SELECT statements fail. Additionally, any INSERT, UPDATE, or DELETE statement that modifies a table with any index in an offline filegroup fails.

When a database is in the RESTORING state, most ALTER DATABASE statements fail. The exception is setting database mirroring options. A database might be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file.

The plan cache for the instance of SQL Server is cleared by setting one of the following options.

Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. This message is logged every five minutes as long as the cache is flushed within that time interval.

The plan cache is also flushed in the following scenarios:

Before you apply a different collation to a database, make sure that the following conditions are in place:

If the following objects, which depend on the database collation, exist in the database, the ALTER DATABASE database_name COLLATE statement fails. SQL Server returns an error message for each object blocking the ALTER action:

Dependency information for non-schema-bound entities is automatically updated when the database collation is changed.

Changing the database collation does not create duplicates among any system names for the database objects. If duplicate names result from the changed collation, the following namespaces can cause the failure of a database collation change:

Duplicate names resulting from the new collation cause the change action to fail, and SQL Server returns an error message specifying the namespace where the duplicate was found.

You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups.

Requires ALTER permission on the database.

The following example changes the name of the AdventureWorks2022 database to Northwind.

The following example creates a database named testdb with the SQL_Latin1_General_CP1_CI_AS collation, and then changes the collation of the testdb database to COLLATE French_CI_AI.

Applies to: SQL Server 2008 (10.0.x) and later.

SQL Server

* SQL Database *  

SQL Managed Instance

Azure SynapseAnalytics

Analytics PlatformSystem (PDW)

In Azure SQL Database, use this statement to modify a database. Use this statement to change the name of a database, change the edition and service objective of the database, join or remove the database to or from an elastic pool, set database options, add or remove the database as a secondary in a geo-replication relationship, and set the database compatibility level.

Because of its length, the ALTER DATABASE syntax is separated into the multiple articles.

ALTER DATABASE The current article provides the syntax and related information for changing the name and other settings of a database.

ALTER DATABASE SET Options Provides the syntax and related information for changing the attributes of a database by using the SET options of ALTER DATABASE.

ALTER DATABASE Compatibility Level Provides the syntax and related information for the SET options of ALTER DATABASE that are related to database compatibility levels.

Is the name of the database to be modified.

CURRENT Designates that the current database in use should be altered.

Renames the database with the name specified as new_database_name. The following example changes the name of a database db1 to db2:

Changes the service tier of the database.

The following example changes edition to Premium:

Important

EDITION change fails if the MAXSIZE property for the database is set to a value outside the valid range supported by that edition.

Changes the storage redundancy of point-in-time restore backups and long-term retention backups (if configured) of the database. The changes are applied to all the future backups taken. Existing backups continue to use the previous setting.

To enforce data residency when you're creating a database by using T-SQL, use LOCAL or ZONE as input to the BACKUP_STORAGE_REDUNDANCY parameter.

Specifies the maximum size of the database. The maximum size must comply with the valid set of values for the EDITION property of the database. Changing the maximum size of the database can cause the database EDITION to be changed.

Note

The MAXSIZE argument does not apply to single databases in the Hyperscale service tier. Hyperscale service tier databases grow as needed, up to 100 TB. The SQL Database service adds storage automatically - you do not need to set a maximum size.

DTU model

1 P11 and P15 allow MAXSIZE up to 4 TB with 1024 GB being the default size. P11 and P15 can use up to 4 TB of included storage at no additional charge. In the Premium tier, MAXSIZE greater than 1 TB is currently available in the following regions: US East2, West US, US Gov Virginia, West Europe, Germany Central, South East Asia, Japan East, Australia East, Canada Central, and Canada East. For more details regarding resource limitations for the DTU model, see DTU resource limits.

The MAXSIZE value for the DTU model, if specified, has to be a valid value shown in the previous table for the service tier specified.

For limits such as maximum data size and tempdb size in the vCore purchasing model, refer to the articles for resource limits for single databases or resource limits for elastic pools.

If no MAXSIZEvalue is set when using the vCore model, the default is 32 GB. For more details regarding resource limitations for vCore model, see vCore resource limits.

The following rules apply to MAXSIZE and EDITION arguments:

Specifies the compute size and service objective.

Specifies the compute size (also known as service level objective, or SLO).

For example, the following sample changes service objective of a Premium tier database in the DTU purchasing model to P6:

For example, the following sample changes service objective of a provisioned compute database in the vCore purchasing model to GP_Gen5_8:

Only for Azure SQL Database Hyperscale. The database name that will be created. Only used by Azure SQL Database Hyperscale named replicas, when SECONDARY_TYPE = NAMED. For more information, see Hyperscale secondary replicas.

Only for Azure SQL Database Hyperscale. GEO specifies a geo-replica, NAMED specifies a named replica. Default is GEO. For more information, see Hyperscale secondary replicas.

For service objective descriptions and more information about the size, editions, and the service objectives combinations, see Compare vCore and DTU-based purchasing models of Azure SQL Database, DTU resource limits and vCore resource limits. Support for PRS service objectives has been removed.

When SERVICE_OBJECTIVE isn't specified, the secondary database is created at the same service level as the primary database. When SERVICE_OBJECTIVE is specified, the secondary database is created at the specified level. The SERVICE_OBJECTIVE specified must be within the same edition as the source. For example, you can't specify S0 if the edition is premium.

To add an existing database to an elastic pool, set the SERVICE_OBJECTIVE of the database to ELASTIC_POOL and provide the name of the elastic pool. You can also use this option to change the database to a different elastic pool within the same server. For more information, see Elastic pools help you manage and scale multiple databases in Azure SQL Database. To remove a database from an elastic pool, use ALTER DATABASE to set the SERVICE_OBJECTIVE to a single database compute size (service objective).

Note

Databases in the Hyperscale service tier can't be added to an elastic pool.

Creates a geo-replication secondary database with the same name on a partner server, making the local database into a geo-replication primary, and begins asynchronously replicating data from the primary to the new secondary. If a database with the same name already exists on the secondary, the command fails. The command is executed on the master database on the server hosting the local database that becomes the primary.

Important

By default, the secondary database is created with the same backup storage redundancy as that of the primary or source database. Changing the backup storage redundancy while creating the secondary isn't supported via T-SQL.

When ALLOW_CONNECTIONS isn't specified, it is set to ALL by default. If it is set ALL, it is a read-only database that allows all logins with the appropriate permissions to connect.

When ELASTIC_POOL isn't specified, the secondary database isn't created in an elastic pool. When ELASTIC_POOL is specified, the secondary database is created in the specified pool.

Important

The user executing the ADD SECONDARY command must be DBManager on primary server, have db_owner membership in local database, and DBManager on secondary server. The client IP address must be added to the allowed list under firewall rules for both the primary and secondary servers. In case of different client IP addresses, the exact same client IP address that has been added on the primary server must also be added to the secondary. This is a required step to be done before running the ADD SECONDARY command to initiate geo-replication.

Removes the specified geo-replicated secondary database on the specified server. The command is executed on the master database on the server hosting the primary database.

Important

The user executing the REMOVE SECONDARY command must be DBManager on the primary server.

Promotes the secondary database in geo-replication partnership on which the command is executed to become the primary and demotes the current primary to become the new secondary. As part of this process, the geo-replication mode is temporarily switched from asynchronous mode to synchronous mode. During the failover process:

This sequence ensures that no data loss occurs. The period during which both databases are unavailable is on the order of 0-25 seconds while the roles are switched. The total operation should take no longer than about one minute. If the primary database is unavailable when this command is issued, the command fails with an error message indicating that the primary database isn't available. If the failover process does not complete and appears stuck, you can use the force failover command and accept data loss - and then, if you need to recover the lost data, call devops (CSS) to recover the lost data.

Important

The user executing the FAILOVER command must be DBManager on both the primary server and the secondary server.

Promotes the secondary database in geo-replication partnership on which the command is executed to become the primary and demotes the current primary to become the new secondary. Use this command only when the current primary is no longer available. It is designed for disaster recovery only, when restoring availability is critical, and some data loss is acceptable.

During a forced failover:

Important

The user executing the FORCE_FAILOVER_ALLOW_DATA_LOSS command must be belong to the dbmanager role on both the primary server and the secondary server.

To remove a database, use DROP DATABASE. To decrease the size of a database, use DBCC SHRINKDATABASE.

The ALTER DATABASE statement must run in auto-commit mode (the default transaction management mode) and isn't allowed in an explicit or implicit transaction.

Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. This message is logged every five minutes as long as the cache is flushed within that time interval.

The procedure cache is also flushed in the following scenario: You run several queries against a database that has default options. Then, the database is dropped.

You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups.

To alter a database, a login must be either the server admin login (created when the Azure SQL Database logical server was provisioned), the Microsoft Entra admin of the server, a member of the dbmanager database role in master, a member of the db_owner database role in the current database, or dbo of the database. Microsoft Entra ID is (formerly Azure Active Directory).

To scale databases via T-SQL, ALTER DATABASE permissions are needed. To scale databases via the Azure portal, PowerShell, Azure CLI, or REST API, Azure RBAC permissions are needed, specifically the Contributor, SQL DB Contributor role, or SQL Server Contributor Azure RBAC roles. For more information, visit Azure built-in roles.

Sets an edition and max size for database db1:

Moves an existing database into a pool named pool1:

Creates a readable secondary database db1 on server secondaryserver of the db1 on the local server.

Removes the secondary database db1 on server secondaryserver.

Promotes a secondary database db1 on server secondaryserver to become the new primary database when executed on server secondaryserver.

Note

For more information, see Disaster recovery guidance - Azure SQL Database and the Azure SQL Database high availability and disaster recovery checklist.

Forces a secondary database db1 on server secondaryserver to become the new primary database when executed on server secondaryserver, in the event that the primary server becomes unavailable. This option can incur data loss.

Updates a single database to the Standard edition (service tier) with a compute size (service objective) of S0 and a maximum size of 250 GB.

Updates the backup storage redundancy of a database to zone-redundant. All future backups of this database use the new setting. This includes point-in-time restore backups and long-term retention backups (if configured).

SQL Server

SQL Database

* SQL Managed Instance *  

Azure SynapseAnalytics

Analytics PlatformSystem (PDW)

In Azure SQL Managed Instance, use this statement to set database options.

Because of its length, the ALTER DATABASE syntax is separated into the multiple articles.

Is the name of the database to be modified.

CURRENT Designates that the current database in use should be altered.

To remove a database, use DROP DATABASE.

To decrease the size of a database, use DBCC SHRINKDATABASE.

The ALTER DATABASE statement must run in auto-commit mode (the default transaction management mode) and isn't allowed in an explicit or implicit transaction.

The plan cache for the Azure SQL Managed Instance is cleared by setting one of the following options.

COLLATE

MODIFY FILEGROUP DEFAULT

MODIFY FILEGROUP READ_ONLY

MODIFY FILEGROUP READ_WRITE

MODIFY NAME

Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. This message is logged every five minutes as long as the cache is flushed within that time interval. The plan cache is also flushed when several queries are executed against a database that has default options. Then, the database is dropped.

Some ALTER DATABASE statements require exclusive lock on a database to be executed. This is why they might fail when another active process is holding a lock on the database. Error that is reported in a case like this is Msg 5061, Level 16, State 1, Line 38 with message ALTER DATABASE failed because a lock could not be placed on database ''. Try again later. This is typically a transient failure and to resolve it, once all locks on the database are released, retry the ALTER DATABASE statement that failed. System view sys.dm_tran_locks holds information on active locks. To check if there are shared or exclusive locks on a database use following query.

You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups.

Only the server-level principal login (created by the provisioning process) or members of the dbcreator database role can alter a database.

Important

The owner of the database can't alter the database unless they are a member of the dbcreator role.

The following examples show you how to set automatic tuning and how to add a file to a database in Azure SQL Managed Instance.

SQL Server

SQL Database

SQL Managed Instance

* Azure SynapseAnalytics *  

Analytics PlatformSystem (PDW)

In Azure Synapse, ALTER DATABASE modifies certain configuration options of a dedicated SQL pool.

Because of its length, the ALTER DATABASE syntax is separated into the multiple articles.

ALTER DATABASE SET options provides the syntax and related information for changing the attributes of a database by using the SET options of ALTER DATABASE.

Specifies the name of the database to be modified.

Renames the database with the name specified as new_database_name.

The 'MODIFY NAME' option has some support limitations in Azure Synapse:

The default is 245,760 GB (240 TB).

Applies to: Optimized for Compute Gen1

The maximum allowable size for the database. The database can't grow beyond MAXSIZE.

Applies to: Optimized for Compute Gen2

The maximum allowable size for rowstore data in the database. Data stored in rowstore tables, a columnstore index's deltastore, or a nonclustered index on a clustered columnstore index can't grow beyond MAXSIZE. Data compressed into columnstore format doesn't have a size limit and isn't constrained by MAXSIZE.

Specifies the compute size (service objective). For more information about service objectives for Azure Synapse, see Data Warehouse Units (DWUs).

Requires these permissions:

The owner of the database can't alter the database unless the owner is a member of the dbmanager role.

The current database must be a different database than the one you are altering, therefore ALTER must be run while connected to the master database.

COMPATIBILITY_LEVEL in SQL Analytics is set to 130 by default and can't be changed. For more information, see ALTER DATABASE compatibility level.

Note

COMPATIBILITY_LEVEL applies to provisioned resources (pools) only.

To run ALTER DATABASE, the database must be online and can't be in a paused state.

The ALTER DATABASE statement must run in auto-commit mode, which is the default transaction management mode. This is set in the connection settings.

The ALTER DATABASE statement can't be part of a user-defined transaction.

You can't change the database collation.

Before you run these examples, make sure the database you are altering isn't the current database. The current database must be a different database than the one you are altering, therefore ALTER must be run while connected to the master database.

SQL Server

SQL Database

SQL Managed Instance

Azure SynapseAnalytics

* AnalyticsPlatform System (PDW) *  

In Analytics Platform System (PDW), ALTER DATABASE modifies the maximum database size options for replicated tables, distributed tables, and the transaction log. Use this statement to manage disk space allocations for a database as it grows or shrinks in size. This article also describes syntax related to setting database options in Analytics Platform System (PDW).

The name of the database to be modified. To display a list of databases on the appliance, use sys.databases.

Updates the AUTOGROW option. When AUTOGROW is ON, Analytics Platform System (PDW) automatically increases the allocated space for replicated tables, distributed tables, and the transaction log as necessary to accommodate growth in storage requirements. When AUTOGROW is OFF, Analytics Platform System (PDW) returns an error if replicated tables, distributed tables, or the transaction log exceeds the maximum size setting.

Specifies the new maximum gigabytes per Compute node for storing all of the replicated tables in the database being altered. If you are planning for appliance storage space, you need to multiply REPLICATED_SIZE by the number of Compute nodes in the appliance.

Specifies the new maximum gigabytes per database for storing all of the distributed tables in the database being altered. The size is distributed across all of the Compute nodes in the appliance.

Specifies the new maximum gigabytes per database for storing all of the transaction logs in the database being altered. The size is distributed across all of the Compute nodes in the appliance.

Sets the database to be encrypted (ON) or not encrypted (OFF). Encryption can only be configured for Analytics Platform System (PDW) when sp_pdw_database_encryption has been set to 1. A database encryption key must be created before transparent data encryption can be configured. For more information about database encryption, see Transparent data encryption (TDE).

When the automatic create statistics option, AUTO_CREATE_STATISTICS, is ON, the Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan. These single-column statistics are created on columns that do not already have a histogram in an existing statistics object.

Default is ON for new databases created after upgrading to AU7. The default is OFF for databases created prior to the upgrade.

For more information about statistics, see Statistics

When the automatic update statistics option, AUTO_UPDATE_STATISTICS, is ON, the query optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. Statistics become out-of-date after operations insert, update, delete, or merge change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.

Default is ON for new databases created after upgrading to AU7. The default is OFF for databases created prior to the upgrade.

For more information about statistics, see Statistics.

The asynchronous statistics update option, AUTO_UPDATE_STATISTICS_ASYNC, determines whether the Query Optimizer uses synchronous or asynchronous statistics updates. The AUTO_UPDATE_STATISTICS_ASYNC option applies to statistics objects created for indexes, single columns in query predicates, and statistics created with the CREATE STATISTICS statement.

Default is ON for new databases created after upgrading to AU7. The default is OFF for databases created prior to the upgrade.

For more information about statistics, see Statistics.

Requires the ALTER permission on the database.

If auto-stats is disabled and you try to alter the statistics settings, PDW outputs the error This option isn't supported in PDW. The system administrator can enable auto-stats by enabling the feature switch AutoStatsEnabled.

The values for REPLICATED_SIZE, DISTRIBUTED_SIZE, and LOG_SIZE can be greater than, equal to, or less than the current values for the database.

Grow and shrink operations are approximate. The resulting actual sizes can vary from the size parameters.

Analytics Platform System (PDW) does not perform the ALTER DATABASE statement as an atomic operation. If the statement is aborted during execution, changes that have already occurred will remain.

The statistics settings only work if the administrator has enable auto-stats. If you are an administrator, use the feature switch AutoStatsEnabled to enable or disable auto-stats.

Takes a shared lock on the DATABASE object. You can't alter a database that is in use by another user for reading or writing. This includes sessions that have issued a USE statement on the database.

Shrinking a database can take a large amount of time and system resources, depending on the size of the actual data within the database, and the amount of fragmentation on disk. For example, shrinking a database could take several hours or more.

Use the following query to determine progress of database transparent data encryption as a percent:

For a comprehensive example demonstrating all the steps in implementing TDE, see Transparent data encryption (TDE).

Set AUTOGROW to ON for database CustomerSales.

The following example sets the replicated table storage limit to 1 GB for the database CustomerSales. This is the storage limit per Compute node.

The following example sets the distributed table storage limit to 1000 GB (one terabyte) for the database CustomerSales. This is the combined storage limit across the appliance for all of the Compute nodes, not the storage limit per Compute node.

The following example updates the database CustomerSales to have a maximum SQL Server transaction log size of 10 GB for the appliance.

The following query returns the current statistics values for all databases. The value 1 means the feature is on, and a 0 means the feature is off.

Use the following statement to enable create and update statistics automatically and asynchronously for database, CustomerSales. This creates and updates single-column statistics as necessary to create high-quality query plans.